
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

public class SqlExecute extends JFrame{

	//private static String url = "jdbc:oracle:thin:";
	private static String url = "jdbc:mysql:";
	private static String driver = "com.mysql.cj.jdbc.Driver";
	private static String user;
	private static String password;
	private static Connection conn;
	private JButton b_conn;
	private JButton b_execute;
	private JLabel l_addr;
	private JLabel l_user;
	private JLabel l_pwd;
	private JLabel l_sid;
	private JTextField t_addr;
	private JTextField t_user;
	private JPasswordField t_pwd;
	private JTextField t_sid;
	private JTextArea t_sql;
	private JTable t_show;
	private JScrollPane jsp;
	
	public SqlExecute() {
		b_conn = new JButton("连接");
		b_execute = new JButton("ִ执行");
		l_addr = new JLabel("主机地址:");
		l_user = new JLabel("用户名:");
		l_pwd = new JLabel("密码:");
		l_sid = new JLabel("sid:");
		t_addr = new JTextField("localhost");
		t_user = new JTextField();
		t_pwd = new JPasswordField();
		t_sid = new JTextField("test");
		t_sql = new JTextArea();
		
		Vector<Vector<String>>  data = new Vector<>();
		Vector<String> columnNames = new Vector<>();
		
		TableModel dm = new DefaultTableModel(data, columnNames);
		t_show = new JTable(dm);
		jsp = new JScrollPane(t_show);
		
		/*
		 * 设置窗口属性
		 */
		this.setBounds(200, 200, 800, 800);
		this.setLayout(null);
		this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		
		//设置组件大小
		l_addr.setBounds(10, 20, 100, 25);
		t_addr.setBounds(120, 20, 100, 25);
		l_user.setBounds(10, 55, 100, 25);
		t_user.setBounds(120, 55, 100, 25);
		l_pwd.setBounds(10, 90, 100, 25);
		t_pwd.setBounds(120, 90, 100, 25);
		l_sid.setBounds(10, 125, 100, 25);
		t_sid.setBounds(120, 125, 100, 25);
		b_conn.setBounds(120, 160, 60, 25);
		t_sql.setBounds(240, 20, 520, 500);
		jsp.setBounds(240, 530, 520, 220);
		b_execute.setBounds(150, 530, 60, 25);
		
		this.add(b_conn);
		this.add(b_execute);
		this.add(l_addr);
		this.add(l_pwd);
		this.add(l_user);
		this.add(l_sid);
		this.add(t_addr);
		this.add(t_pwd);
		this.add(jsp);
		this.add(t_sid);
		this.add(t_user);
		this.add(t_sql);
		
		
		//设置事件
		b_conn.addActionListener(new ConnListener());
		b_execute.addActionListener(new SqlListener());
	}
	private class SqlListener implements ActionListener{
		public void actionPerformed(ActionEvent e) {
			//1-获取输入的sql语句
			String sql = t_sql.getText();
			//2-
			SqlDao sd = new SqlDao(conn);
			
			//获取执行操作
			sql = sql.trim();//去除空格
			String start = sql.substring(0, sql.indexOf(" "));
			
			if(null != start) {
				if("insert".equalsIgnoreCase(start) || "delete".equalsIgnoreCase(start)||"update".equalsIgnoreCase(start)) {
					int count = sd.update(sql);
				}else if("select".equalsIgnoreCase(start)) {
					Vector<Vector<String>> datas = sd.select(sql);
					//将data显示到数据区
					DefaultTableModel tm = (DefaultTableModel)t_show.getModel();
					
					Vector<String> columns = new Vector<>();
					columns.add("userid");
					columns.add("username");
					columns.add("password");
					columns.add("email");
					
					tm.setDataVector(datas, columns);
					t_show.repaint();
				}else {
					JOptionPane.showMessageDialog(null, "这不是一个SQL语句");
				}
			}
		}
	}

	private class ConnListener implements ActionListener{

		@Override
		public void actionPerformed(ActionEvent e) {
			String addr = t_addr.getText();
			user = t_user.getText();
			password = String.valueOf(t_pwd.getPassword());
			String sid = t_sid.getText();
			try {
				Class.forName(driver);
				//jdbc:oracle:thin:@localhost:1521:orcl
				url = url+"//"+addr+":3306/"+sid+"?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true";
				System.out.println(url);
				conn = DriverManager.getConnection(url, user, password);
				System.out.println(conn);
				if(null != conn) {
					JOptionPane.showMessageDialog(null, "连接成功");
					b_conn.setEnabled(false);
					t_addr.setEditable(false);
					t_user.setEditable(false);
					t_pwd.setEditable(false);
					t_sid.setEditable(false);
				}
			} catch (ClassNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			
		}
		
	}
	
}
